﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using YunSpace.Core;
using YunSpace.Domain.Entity.WorkFlow;
using YunSpace.Domain.IRepository.WorkFlow;
using YunSpace.Repository.WorkFlow;

namespace YunSpace.Application.WorkFlow
{
    public class DBConnectionApp
    {
        private string databaseName;
        private Log Log;
        private IDBConnectionRepository dataDBConnection;
        public DBConnectionApp()
        {
            this.dataDBConnection = new DBConnectionRepository();
            this.Log = LogFactory.GetLogger("LogFactory");
            this.databaseName = Configs.GetValue("DatabaseName");
        }

        /// <summary>
        /// 连接类型
        /// </summary>
        public enum Types
        {
            MySql
        }

        /// <summary>
        /// 新增
        /// </summary>
        public int Add(DBConnectionEntity model)
        {
            int i = dataDBConnection.Add(model);
            ClearCache();
            return i;
        }
        /// <summary>
        /// 更新
        /// </summary>
        public int Update(DBConnectionEntity model)
        {
            int i = dataDBConnection.Update(model);
            ClearCache();
            return i;
        }
        /// <summary>
        /// 查询所有记录
        /// </summary>
        public List<DBConnectionEntity> GetAll(bool fromCache = false)
        {
            if (!fromCache)
            {
                return dataDBConnection.GetAll();
            }
            else
            {
                string key = Utility.Keys.CacheKeys.DBConnnections.ToString();
                object obj = CacheFactory.Cache().GetCache<List<DBConnectionEntity>>(key);

                if (obj != null && obj is List<DBConnectionEntity>)
                {
                    return obj as List<DBConnectionEntity>;
                }
                else
                {
                    var list = dataDBConnection.GetAll();
                    CacheFactory.Cache().WriteCache<List<DBConnectionEntity>>(list, key);
                    return list;
                }
            }
        }
        /// <summary>
        /// 查询单条记录
        /// </summary>
        public DBConnectionEntity Get(Guid id)
        {
            return dataDBConnection.Get(id);
        }
        /// <summary>
        /// 删除
        /// </summary>
        public int Delete(Guid id)
        {
            int i = dataDBConnection.Delete(id);
            ClearCache();
            return i;
        }
        /// <summary>
        /// 查询记录条数
        /// </summary>
        public long GetCount()
        {
            return dataDBConnection.GetCount();
        }
        /// <summary>
        /// 连接类型
        /// </summary>
        public enum ConnTypes
        {
            MySql
        }
        /// <summary>
        /// 得到所有数据连接类型的下拉选择
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        public string GetAllTypeOptions(string value = "")
        {
            StringBuilder options = new StringBuilder();
            var array = Enum.GetValues(typeof(ConnTypes));
            foreach (var arr in array)
            {
                options.AppendFormat("<option value=\"{0}\" {1}>{0}</option>", arr, arr.ToString() == value ? "selected=\"selected\"" : "");
            }
            return options.ToString();
        }
        /// <summary>
        /// 得到所有数据连接的下拉选择
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        public string GetAllOptions(string value = "")
        {
            var conns = GetAll(true);
            StringBuilder options = new StringBuilder();
            foreach (var conn in conns)
            {
                options.AppendFormat("<option value=\"{0}\" {1}>{2}</option>", conn.F_Id,
                    string.Compare(conn.F_Id.ToString(), value, true) == 0 ? "selected=\"selected\"" : "", conn.F_Name);
            }
            return options.ToString();
        }
        /// <summary>
        /// 清除缓存
        /// </summary>
        public void ClearCache()
        {
            string key = Utility.Keys.CacheKeys.DBConnnections.ToString();
            CacheFactory.Cache().RemoveCache(key);
        }

        /// <summary>
        /// 根据连接ID得到所有表
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<string> GetTables(string id)
        {
            var allConns = GetAll(true);
            var conn = allConns.Find(p => p.F_Id == id);
            if (conn == null) return new List<string>();
            List<string> tables = new List<string>();
            switch (conn.F_Type)
            {
                case "MySql":
                    tables = getTables_MySql(conn);
                    break;
            }
            return tables;
        }
        /// <summary>
        /// 得到所有字段
        /// </summary>
        /// <param name="id">连接ID</param>
        /// <param name="table">表名</param>
        /// <returns></returns>
        public Dictionary<string, string> GetFields(string id, string table)
        {
            if (table.IsNullOrEmpty()) return new Dictionary<string, string>();
            var allConns = GetAll(true);
            var conn = allConns.Find(p => p.F_Id == id);
            if (conn == null) return new Dictionary<string, string>();
            Dictionary<string, string> fields = new Dictionary<string, string>();
            switch (conn.F_Type)
            {
                case "MySql":
                    fields = getFields_MySql(conn, table);
                    break;
            }
            return fields;
        }
        /// <summary>
        /// 得到一个连接一个表一个字段的值
        /// </summary>
        /// <param name="link_table_field"></param>
        /// <returns></returns>
        public string GetFieldValue(string link_table_field, Dictionary<string, string> pkFieldValue)
        {
            if (link_table_field.IsNullOrEmpty()) return "";
            string[] array = link_table_field.Split('.');
            if (array.Length != 3) return "";
            string link = array[0];
            string table = array[1];
            string field = array[2];
            var allConns = GetAll(true);
            //Guid linkid;
            //if (!link.IsGuid(out linkid)) return "";
            var conn = allConns.Find(p => p.F_Id == link);
            if (conn == null) return "";
            List<string> fields = new List<string>();
            string value = string.Empty;
            switch (conn.F_Type)
            {
                case "MySql":
                    value = getFieldValue_MySql(conn, table, field, pkFieldValue);
                    break;
            }
            return value;
        }

        /// <summary>
        /// 得到一个连接一个表一个字段的值
        /// </summary>
        /// <param name="link_table_field"></param>
        /// <returns></returns>
        public string GetFieldValue(string link_table_field, string pkField, string pkFieldValue)
        {
            if (link_table_field.IsNullOrEmpty())
            {
                return "";
            }
            string[] array = link_table_field.Split('.');
            if (array.Length != 3)
            {
                return "";
            }
            string link = array[0];
            string table = array[1];
            string field = array[2];
            var allConns = GetAll(true);
            //Guid linkid;
            //if (!link.IsGuid(out linkid))
            //{
            //    return "";
            //}
            var conn = allConns.Find(p => p.F_Id == link);
            if (conn == null)
            {
                return "";
            }
            string value = string.Empty;
            switch (conn.F_Type)
            {
                case "MySql":
                    value = getFieldValue_MySql(conn, table, field, pkField, pkFieldValue);
                    break;
            }
            return value;
        }
        /// <summary>
        /// 测试一个连接
        /// </summary>
        /// <param name="connID"></param>
        /// <returns></returns>
        public string Test(Guid connID)
        {
            var link = Get(connID);
            if (link == null) return "未找到连接!";
            switch (link.F_Type)
            {
                case "MySql":
                    return test_MySql(link);

            }

            return "";
        }

        /// <summary>
        /// 测试一个连接
        /// </summary>
        /// <param name="conn"></param>
        /// <returns></returns>
        private string test_MySql(DBConnectionEntity conn)
        {
            using (var sqlConn = new MySqlConnection(conn.F_ConnectionString))
            {
                try
                {
                    sqlConn.Open();
                    return "连接成功!";
                }
                catch (MySqlException err)
                {
                    return err.Message;
                }
            }
        }

        /// <summary>
        /// 测试一个sql条件合法性
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="where"></param>
        /// <returns></returns>
        private string testSql_MySql(DBConnectionEntity conn, string sql)
        {
            using (MySqlConnection sqlConn = new MySqlConnection(conn.F_ConnectionString))
            {
                try
                {
                    sqlConn.Open();
                }
                catch (MySqlException err)
                {
                    return err.Message;
                }
                using (MySqlCommand cmd = new MySqlCommand(sql, sqlConn))
                {
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (MySqlException err)
                    {
                        return err.Message;
                    }
                }
                return "";
            }
        }

        /// <summary>
        /// 得到一个连接所有表
        /// </summary>
        /// <param name="conn"></param>
        /// <returns></returns>
        private List<string> getTables_MySql(DBConnectionEntity conn)
        {
            using (MySqlConnection sqlConn = new MySqlConnection(conn.F_ConnectionString))
            {
                try
                {
                    sqlConn.Open();
                }
                catch (MySqlException err)
                {
                    Log.Error(err);
                    return new List<string>();
                }
                List<string> tables = new List<string>(); 
                string sql = string.Format(@"SELECT TABLE_NAME name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '{0}' 
                                                        AND TABLE_TYPE = 'BASE TABLE' ORDER BY name", this.databaseName);
                using (MySqlCommand sqlCmd = new MySqlCommand(sql, sqlConn))
                {
                    MySqlDataReader dr = sqlCmd.ExecuteReader();
                    while (dr.Read())
                    {
                        tables.Add(dr.GetString(0));
                    }
                    dr.Close();
                    return tables;
                }
            }
        }
        /// <summary>
        /// 得到一个连接一个表所有字段
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="table"></param>
        /// <returns></returns>
        private Dictionary<string, string> getFields_MySql(DBConnectionEntity conn, string table)
        {
            using (MySqlConnection sqlConn = new MySqlConnection(conn.F_ConnectionString))
            {
                try
                {
                    sqlConn.Open();
                }
                catch (MySqlException err)
                {
                    Log.Error(err);
                    return new Dictionary<string, string>();
                }
                Dictionary<string, string> fields = new Dictionary<string, string>();
                //                string sql = string.Format(@"SELECT a.name as f_name, b.value from 
                //sys.syscolumns a LEFT JOIN sys.extended_properties b on a.id=b.major_id AND a.colid=b.minor_id AND b.name='MS_Description' 
                //WHERE object_id('{0}')=a.id ORDER BY a.colid", table);
                string sql = string.Format(@"SELECT COLUMN_NAME as f_name,COLUMN_COMMENT value  FROM INFORMATION_SCHEMA.COLUMNS
                                    WHERE TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}' ORDER BY ORDINAL_POSITION", this.databaseName, table);
                using (MySqlCommand sqlCmd = new MySqlCommand(sql, sqlConn))
                {
                    MySqlDataReader dr = sqlCmd.ExecuteReader();
                    while (dr.Read())
                    {
                        fields.Add(dr.GetString(0), dr.IsDBNull(1) ? "" : dr.GetString(1));
                    }
                    dr.Close();
                    return fields;
                }
            }
        }

        /// <summary>
        /// 得到一个连接一个表一个字段的值
        /// </summary>
        /// <param name="conn">连接ID</param>
        /// <param name="table">表名</param>
        /// <param name="field">字段名</param>
        /// <param name="pkFieldValue">主键和值字典</param>
        /// <returns></returns>
        private string getFieldValue_MySql(DBConnectionEntity conn, string table, string field, Dictionary<string, string> pkFieldValue)
        {
            using (MySqlConnection sqlConn = new MySqlConnection(conn.F_ConnectionString))
            {
                try
                {
                    sqlConn.Open();
                }
                catch (MySqlException err)
                {
                    Log.Error(err);
                    return "";
                }
                List<string> fields = new List<string>();
                StringBuilder sql = new StringBuilder();
                sql.AppendFormat("select {0} from {1} where 1=1", field, table);
                foreach (var pk in pkFieldValue)
                {
                    sql.AppendFormat(" and {0}='{1}'", pk.Key, pk.Value);
                }

                using (MySqlCommand sqlCmd = new MySqlCommand(sql.ToString(), sqlConn))
                {
                    MySqlDataReader dr = sqlCmd.ExecuteReader();
                    string value = string.Empty;
                    if (dr.HasRows)
                    {
                        dr.Read();
                        value = dr.GetString(0);
                    }
                    dr.Close();
                    return value;
                }
            }
        }

        /// <summary>
        /// 得到一个连接一个表一个字段的值
        /// </summary>
        /// <param name="linkID">连接ID</param>
        /// <param name="table">表</param>
        /// <param name="field">字段</param>
        /// <param name="pkField">主键字段</param>
        /// <param name="pkFieldValue">主键值</param>
        /// <returns></returns>
        private string getFieldValue_MySql(DBConnectionEntity conn, string table, string field, string pkField, string pkFieldValue)
        {
            string v = "";
            using (MySqlConnection sqlConn = new MySqlConnection(conn.F_ConnectionString))
            {
                try
                {
                    sqlConn.Open();
                }
                catch (MySqlException err)
                {
                    Log.Error(err);
                    return "";
                }
                string sql = string.Format("SELECT {0} FROM {1} WHERE {2} = '{3}'", field, table, pkField, pkFieldValue);
                using (MySqlDataAdapter dap = new MySqlDataAdapter(sql, sqlConn))
                {
                    try
                    {
                        DataTable dt = new DataTable();
                        dap.Fill(dt);
                        if (dt.Rows.Count > 0)
                        {
                            v = dt.Rows[0][0].ToString();
                        }
                    }
                    catch (MySqlException err)
                    {
                        Log.Error(err);
                    }
                    return v;
                }
            }
        }

        /// <summary>
        /// 根据连接实体得到连接
        /// </summary>
        /// <param name="linkID"></param>
        /// <returns></returns>
        public System.Data.IDbConnection GetConnection(DBConnectionEntity dbconn)
        {
            if (dbconn == null || dbconn.F_Type.IsNullOrEmpty() || dbconn.F_ConnectionString.IsNullOrEmpty())
            {
                return null;
            }
            IDbConnection conn = null;
            switch (dbconn.F_Type)
            {
                case "MySql":
                    conn = new MySqlConnection(dbconn.F_ConnectionString);
                    break;
            }

            return conn;

        }

        /// <summary>
        /// 根据连接实体得到数据适配器
        /// </summary>
        /// <param name="linkID"></param>
        /// <returns></returns>
        public System.Data.IDbDataAdapter GetDataAdapter(IDbConnection conn, string connType, string cmdText, IDataParameter[] parArray)
        {
            IDbDataAdapter dataAdapter = null;
            switch (connType)
            {
                case "MySql":
                    using (MySqlCommand cmd = new MySqlCommand(cmdText, (MySqlConnection)conn))
                    {
                        if (parArray != null && parArray.Length > 0)
                        {
                            cmd.Parameters.AddRange(parArray);
                        }
                        dataAdapter = new MySqlDataAdapter(cmd);
                    }
                    break;
            }
            return dataAdapter;
        }

        /// <summary>
        /// 测试一个sql是否合法
        /// </summary>
        /// <param name="dbconn"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public bool TestSql(DBConnectionEntity dbconn, string sql)
        {
            if (dbconn == null)
            {
                return false;
            }
            switch (dbconn.F_Type)
            {
                case "MySql":
                    using (MySqlConnection conn = new MySqlConnection(dbconn.F_ConnectionString))
                    {
                        try
                        {
                            conn.Open();
                        }
                        catch
                        {
                            return false;
                        }
                        using (MySqlCommand cmd = new MySqlCommand(sql.ReplaceSelectSql(), (MySqlConnection)conn))
                        {
                            try
                            {
                                cmd.ExecuteNonQuery();
                                return true;
                            }
                            catch
                            {
                                return false;
                            }
                        }
                    }
                    break;
            }
            return false;
        }

        /// <summary>
        /// 根据连接实体得到数据表
        /// </summary>
        /// <param name="linkID"></param>
        /// <returns></returns>
        public System.Data.DataTable GetDataTable(DBConnectionEntity dbconn, string sql)
        {
            if (dbconn == null || dbconn.F_Type.IsNullOrEmpty() || dbconn.F_ConnectionString.IsNullOrEmpty())
            {
                return null;
            }
            DataTable dt = new DataTable();
            switch (dbconn.F_Type)
            {
                case "MySql":
                    using (MySqlConnection conn = new MySqlConnection(dbconn.F_ConnectionString))
                    {
                        try
                        {
                            conn.Open();
                            using (MySqlDataAdapter dap = new MySqlDataAdapter(sql, conn))
                            {
                                dap.Fill(dt);
                            }
                        }
                        catch (MySqlException ex)
                        {
                            Log.Error(ex);
                        }
                    }
                    break;

            }

            return dt;
        }

        /// <summary>
        /// 得到一个表的结构
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="tableName"></param>
        /// <param name="dbType"></param>
        /// <returns></returns>
        public System.Data.DataTable GetTableSchema(System.Data.IDbConnection conn, string tableName, string dbType)
        {
            DataTable dt = new DataTable();
            switch (dbType)
            {
                case "MySql":
                    //string sql = string.Format(@"select a.name as f_name,b.name as t_name,[length],a.isnullable as is_null, a.cdefault as cdefault,COLUMNPROPERTY( OBJECT_ID('{0}'),a.name,'IsIdentity') as isidentity from 
                    //sys.syscolumns a inner join sys.types b on b.user_type_id=a.xtype 
                    //where object_id('{0}')=id order by a.colid", tableName);
                    string sql = string.Format(@"SELECT COLUMN_NAME f_name, DATA_TYPE t_name,ifnull(CHARACTER_OCTET_LENGTH, -1) length, ifnull(COLUMN_DEFAULT, 0) cdefault ,case when IS_NULLABLE = 'YES' then 1 else 0 end  is_null, null IsIdentity
                                    FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}' ORDER BY ORDINAL_POSITION", this.databaseName, tableName);
                    MySqlDataAdapter dap = new MySqlDataAdapter(sql, (MySqlConnection)conn);
                    dap.Fill(dt);
                    break;
            }
            return dt;
        }

        /// <summary>
        /// 更新一个连接一个表一个字段的值
        /// </summary>
        /// <param name="connID"></param>
        /// <param name="table"></param>
        /// <param name="field"></param>
        /// <param name="value"></param>
        public void UpdateFieldValue(Guid connID, string table, string field, string value, string where)
        {
            var conn = Get(connID);
            if (conn == null)
            {
                return;
            }
            switch (conn.F_Type)
            {
                case "MySql":
                    using (var dbconn = GetConnection(conn))
                    {
                        try
                        {
                            dbconn.Open();
                        }
                        catch (MySqlException ex)
                        {
                            Log.Error(ex);
                        }
                        string sql = string.Format("UPDATE {0} SET {1}=@value WHERE {2}", table, field, where);
                        MySqlParameter par = new MySqlParameter("@value", value);
                        using (MySqlCommand cmd = new MySqlCommand(sql, (MySqlConnection)dbconn))
                        {
                            cmd.Parameters.Add(par);
                            try
                            {
                                cmd.ExecuteNonQuery();
                            }
                            catch (MySqlException ex)
                            {
                                Log.Error(ex);
                            }
                        }
                    }
                    break;
            }
        }

    }
}
